using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Colorprj.Masters;
using System.Data.Common;
using Colorprj.SalesReturn;
using Colorprj.Common;
namespace Colorprj.SalesReturn
{
    class SalesReturnManager
    {


        ///// <summary>
        ///// Transfers an amount between two accounts.
        ///// </summary>
        ///// <param name="transactionAmount">Amount to transfer.</param>
        ///// <param name="sourceAccount">Account to be credited.</param>
        ///// <param name="destinationAccount">Account to be debited.</param>
        ///// <returns>true if sucessful; otherwise false.</returns>
        ///// <remarks>Demonstrates executing multiple updates within the 
        ///// context of a transaction.</remarks>
        public bool AddSalesReturn(SalesReturnHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "AddSalesReturnHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "SalesReturnNo", DbType.String, phEntity.SalesReturnNo);
            db.AddInParameter(creditCommand, "SalesReturnDate", DbType.DateTime, phEntity.SalesReturnDate);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, phEntity.BuyerId);
            db.AddInParameter(creditCommand, "TotalAmount", DbType.Double, phEntity.TotalAmount);
            db.AddInParameter(creditCommand, "TaxAmount", DbType.Double, phEntity.TaxAmount);
            db.AddInParameter(creditCommand, "SRAmount", DbType.Double, phEntity.SRAmount);


            List<SalesReturnDetailEntity> SalesReturnDetailList = phEntity.SalesReturnDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int SalesReturnHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddSalesReturnDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (SalesReturnDetailEntity SalesReturn in SalesReturnDetailList)
                    {
                        DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                        db.AddInParameter(debitCommand, "SalesReturnId", DbType.Int32, SalesReturnHeadId);
                        //db.AddInParameter(debitCommand, "ProductId", DbType.Int32, SalesReturn.ProductId);
                        db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, SalesReturn.SubproductId);
                        db.AddInParameter(debitCommand, "Qty", DbType.Decimal, SalesReturn.Qty);
                        db.AddInParameter(debitCommand, "Rate", DbType.Decimal, SalesReturn.Rate);
                        db.AddInParameter(debitCommand, "Amount", DbType.Decimal, SalesReturn.Amount);

                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception e)
                {
                    // Rollback transaction
                    //throw new Exception(e.Message) ; 
                    transaction.Rollback();
                }
                connection.Close();

                return result;
            }
        }


        ///// <summary>
        ///// Transfers an amount between two accounts.
        ///// </summary>
        ///// <param name="transactionAmount">Amount to transfer.</param>
        ///// <param name="sourceAccount">Account to be credited.</param>
        ///// <param name="destinationAccount">Account to be debited.</param>
        ///// <returns>true if sucessful; otherwise false.</returns>
        ///// <remarks>Demonstrates executing multiple updates within the 
        ///// context of a transaction.</remarks>
        public bool UpdSalesReturn(SalesReturnHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdSalesReturnHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "SalesReturnNo", DbType.String, phEntity.SalesReturnNo);
            db.AddInParameter(creditCommand, "SalesReturnDate", DbType.DateTime, phEntity.SalesReturnDate);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, phEntity.BuyerId);
            db.AddInParameter(creditCommand, "SalesReturnId", DbType.Int32, phEntity.SalesReturnHeadId);
            db.AddInParameter(creditCommand, "TotalAmount", DbType.Double, phEntity.TotalAmount);



            List<SalesReturnDetailEntity> SalesReturnDetailList = phEntity.SalesReturnDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int SalesReturnHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddSalesReturnDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (SalesReturnDetailEntity SalesReturn in SalesReturnDetailList)
                    {
                        DbCommand debitCommand;
                        if (SalesReturn.SalesReturnDetailId == 0)
                        {
                            debitCommand = db.GetStoredProcCommand(sqlCommand);
                        }
                        else
                        {
                            if (SalesReturn.Qty == 0) { debitCommand = db.GetStoredProcCommand("DelSalesReturnDetail"); }

                            else
                            {
                                debitCommand = db.GetStoredProcCommand("UpdSalesReturnDetail");
                                db.AddInParameter(debitCommand, "OldQty", DbType.Decimal, SalesReturn.OldQty);
                            }
                            db.AddInParameter(debitCommand, "SalesReturnDetailId", DbType.Decimal, SalesReturn.SalesReturnDetailId);
                        }
                        if (SalesReturn.Qty != 0)
                        {
                            db.AddInParameter(debitCommand, "SalesReturnId", DbType.Int32, phEntity.SalesReturnHeadId);
                            db.AddInParameter(debitCommand, "ProductId", DbType.Int32, SalesReturn.ProductId);
                            db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, SalesReturn.SubproductId);
                            db.AddInParameter(debitCommand, "Qty", DbType.Decimal, SalesReturn.Qty);
                            db.AddInParameter(debitCommand, "Rate", DbType.Decimal, SalesReturn.Rate);
                            db.AddInParameter(debitCommand, "Amount", DbType.Decimal, SalesReturn.Amount);
                        }
                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception e)
                {
                    // Rollback transaction
                    //throw new Exception(e.Message) ; 
                    transaction.Rollback();
                }
                connection.Close();

                return result;
            }
        }



        public List<SalesReturnHeadEntity> GetUnInvSalesReturnList(int BuyerId)
        {
            bool result = false;
            List<SalesReturnHeadEntity> SalesReturnList = new List<SalesReturnHeadEntity>();
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "SelUnInvSalesReturn";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, BuyerId);
            DataSet SalesReturnchaseDataSet = db.ExecuteDataSet(creditCommand);
            foreach (DataRow dr in SalesReturnchaseDataSet.Tables[0].Rows)
            {
                SalesReturnHeadEntity phEntity = new SalesReturnHeadEntity();
                phEntity.BuyerId = Convert.ToInt16(dr["BuyerId"]);
                phEntity.SalesReturnHeadId = Convert.ToInt16(dr["SalesReturnId"]);
                phEntity.SalesReturnNo = dr["SalesReturnNo"].ToString();
                phEntity.SalesReturnDate = Convert.ToDateTime(dr["SalesReturnDate"]);
                SalesReturnList.Add(phEntity); 
            }

            return SalesReturnList;
        }


        public SalesReturnHeadEntity GetSalesReturn(int SalesReturnId)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelSalesReturnHeadDet";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "SalesReturnHeadId", DbType.Int32, SalesReturnId);

            DataSet SalesReturnchaseDataSet = db.ExecuteDataSet(creditCommand);
            SalesReturnHeadEntity phEntity = new SalesReturnHeadEntity();
            List<SalesReturnDetailEntity> SalesReturnDetailList = new List<SalesReturnDetailEntity>();
            foreach (DataRow dr in SalesReturnchaseDataSet.Tables[0].Rows)
            {
                phEntity.BuyerId = Convert.ToInt16(dr["BuyerId"]);
                phEntity.SalesReturnHeadId = Convert.ToInt16(dr["SalesReturnId"]);
                phEntity.SalesReturnNo = dr["SalesReturnNo"].ToString();
                phEntity.SalesReturnDate = Convert.ToDateTime(dr["SalesReturnDate"]);
                phEntity.InvoiceId = Utility.getIntVal(dr["InvoiceId"]);
                phEntity.Printed = Utility.getBoolVal(dr["Printed"]);
                phEntity.TotalAmount  = Utility.getDblVal(dr["TotalAmount"]);

           
                SalesReturnDetailEntity pdEntity = new SalesReturnDetailEntity();
                pdEntity.ProductId = Convert.ToInt16(dr["ProductId"]);
                pdEntity.SubproductId = Convert.ToInt16(dr["SubproductId"]);
                pdEntity.Qty = Utility.getDblVal (dr["Qty"]);
                pdEntity.OldQty = Utility.getDblVal(dr["Qty"]);
                pdEntity.Rate = Convert.ToDouble(dr["Rate"]);
                pdEntity.Amount = Convert.ToDouble(dr["Amount"]);
                pdEntity.SalesReturnDetailId = Convert.ToInt16(dr["SalesReturnDetailId"]);
                SalesReturnDetailList.Add(pdEntity);
            }
            phEntity.SalesReturnDetailList = SalesReturnDetailList;
            return phEntity;
        }

        public SubProduct GetSubProductById(int Id)
        {
            SubProductManager spm = new SubProductManager();

            return spm.GetSubProductById(Id);
        }

        public int GetLastSalesReturnNo()
        {
            int result = 0;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            string sqlCommand = "SelLastSalesReturnNo";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            DataSet PurchaseDataSet = db.ExecuteDataSet(creditCommand);
            result = Convert.ToInt16(PurchaseDataSet.Tables[0].Rows[0]["MaxSalesReturnId"]);

            return result;
        }

        public void UpdPrintFlg(int SalesReturnId)
        {
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdSalesReturnWithPrintFlag";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "SalesReturnId", DbType.Int32, SalesReturnId);
            db.ExecuteNonQuery(creditCommand);
        }


    }
}
